#!/usr/bin/python3
# -*- coding: utf-8 -*-
# @author： tester
# @file： handleDatabase.py
# datetime： 2021/7/15 14:53 
# ide： PyCharm

import logging
import pymssql
from comm.utils.readYaml import read_yaml_data
from config import DB_CONFIG, PROJECT_NAME

dbcfg = read_yaml_data(DB_CONFIG)[PROJECT_NAME]


def innit_connect():
    # 获取配置信息
    server = dbcfg['sqlServer_info']['server']
    port = int(dbcfg['sqlServer_info']['port'])
    user = dbcfg['sqlServer_info']['user']
    password = dbcfg['sqlServer_info']['password']
    db = dbcfg['sqlServer_info']['db']
    # 初始化连接
    conn = pymssql.connect(host=server, port=port, user=user, password=password, database=db)
    cur = conn.cursor(True)
    return conn, cur


def query_sqlserver(sql, count=None):
    """查询SQL数据
    :param sql: sql查询语句
    :param count: 查询数据条数
    :return:
    """
    logging.info('执行查询>>> {}'.format(sql))
    conn, cur = innit_connect()
    cur.execute(sql)
    if count is not None:
        res = cur.fetchmany(count)
    else:
        res = cur.fetchall()
    cur.close()
    conn.close()
    return res


def handle_data(sql):
    """ 增删改查数据
    :param sql: sql执行语句
    """
    logging.info('执行增删改>>> {}'.format(sql))
    conn, cur = innit_connect()
    try:
        cur.execute(sql)
        conn.commit()
    except AssertionError as e:
        raise e
    cur.close()
    conn.close()


if __name__ == '__main__':
    # sql1 = "INSERT INTO [dbo].[test_connect]([id], [vaule]) VALUES (3, 'lili');"
    sql2 = "SELECT TOP 10 templateId,name FROM [dbo].[TEMPLATE_PARAM] WHERE [type] = 'text' AND [templateId] in (SELECT Top 10 [Id] FROM [dbo].[TEMPLATE] WHERE [templateType] = 'HTML_FORM' AND [status] = 0 order by createTime DESC)"
    id = query_sqlserver(sql2)

    print(id)
    # sql3 = "SELECT TOP 10 [name] FROM [dbo].[TEMPLATE_PARAM] WHERE [templateId] = {}".format(id)
    # print(query_sqlserver(sql3))
